from sqlalchemy import create_engine
from django.shortcuts import render
import pandas as pd
from django.views.decorators.csrf import csrf_exempt
import os
import json
import shutil

engine = create_engine('mysql+pymysql://root:717597@localhost:3306/db1')
dict_of_name = []
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))


# 创建上传文件路径
def make_folder(p):
    if os.path.exists(p):
        pass
    else:
        os.mkdir(p)


# 清空原有文件并重建
def remake_folder(p):
    if os.path.exists(p):
        shutil.rmtree(p)
    os.mkdir(p)


# 定义读写函数
def index(request):
    if request.method == 'POST':
        # 读取上传文件的字节流
        for file_obj in request.FILES.getlist("myFile"):
            local_path = BASE_DIR + '\\media'
            make_folder(local_path)
            file_name = local_path + '\\' + file_obj.name
            dict_of_name.append(file_obj.name)
            # 写入保存文件
            with open(file_name, "wb") as f:
                for line in file_obj:
                    f.write(line)

    return render(request, "realindex.html",
                  {'dict': dict_of_name, 'list': json.dumps(dict_of_name), 'success': "已经成功提交文件!"})


# 清空原有上传文件
def clear(request):
    dict_of_name.clear()
    local_path = BASE_DIR + '\\media'
    remake_folder(local_path)

    return render(request, 'realindex.html', {'dict': dict_of_name, 'success': "已经成功清除文件!"})


# 处理文件
def deal(request):
    return render(request, 'realindex.html', {'dict': dict_of_name, 'success': '已经成功处理文件！'})


# 查看柱状图片
def three(request):
    return render(request, '柱状图.html')


# 查看环形图片
def circle(request):
    return render(request, '环形图.html')


# 查看交互图片
def exchange(request):
    return render(request, '高级更新.html')


# 查看交互图片
def tree(request):
    return render(request, '决策树.html')


# 查看交互图片
def scdot(request):
    return render(request, '散点图.html')


# 查看总体图片
def show(request):
    return render(request, 'show.html')


def home(request):
    return render(request, 'home.html')


@csrf_exempt
def register(request):
    # 做好数据的Excel表备份
    file = os.path.join(os.path.abspath(os.path.dirname(__file__)), 'new.xlsx')
    # 获取注册网页传过来的数据
    if request.method == 'POST':
        username = request.POST['username']
        email = request.POST['email']
        password = request.POST['password']
        # 创建数据框存储数据方便写入Excel表做备份
        df = pd.DataFrame({'username': username, 'email': email, 'password': password}, index=[1])
        # 获取数据库中所有数据
        sql0 = "select * from record;"
        df1 = pd.read_sql_query(sql0, engine)
        # 判断用户名是否已在数据库中存在
        if username in df1['username'].values:
            message = '用户名已存在！请重新输入！'
            dictionary = json.dumps({'message': message, 'username': username, 'email': email})
            return render(request, 'home.html', {'message': '用户名已存在！请重新输入！', 'dict': dictionary})
        else:
            df1 = df1.append(df, ignore_index=True)
            df1.to_excel(file)
            sql1 = "insert into record values ('" + username + "','" + password + "','" + email + "');"
            engine.execute(sql1)
            message = '注册成功！'
            dictionary = json.dumps({'message': message, 'username': username, 'email': email})
            return render(request, 'home.html', {'message': username + '注册成功！', 'dict': dictionary})


@csrf_exempt
def login(request):
    if request.method == 'POST':
        email = request.POST.get('email')
        password = request.POST.get('password')
        # 获取数据库中所有数据
        sql0 = "select * from record;"
        df1 = pd.read_sql_query(sql0, engine)

        if email not in df1['username'].values and email not in df1['email'].values:
            message = '账户或邮箱不存在！'
            dictionary = json.dumps({'message': message, 'email': email})
            return render(request, 'home.html', {'message': '账户或邮箱不存在！', 'dict': dictionary})
        if (email in df1['username'].values or email in df1['email'].values) and password not in df1['password'].values:
            message = '密码错误！'
            dictionary = json.dumps({'message': message, 'email': email})
            return render(request, 'home.html', {'message': '密码错误！', 'dict': dictionary})
        else:
            return render(request, 'realindex.html', {'success': '登录成功！'})


def change_password_page(request):
    return render(request, 'alterpd.html')


def change_password(request):
    if request.method == 'POST':
        username = request.POST['username']
        email = request.POST.get('email')
        password = request.POST.get('password')
        newpassword = request.POST['newpassword']
        # 获取数据库中所有数据
        sql0 = "select * from record;"
        df1 = pd.read_sql_query(sql0, engine)
        action = True
        message = ""
        if password != newpassword:
            message = '所输入的新密码与再次输入的密码不同！'
            dictionary = json.dumps({'message': message, 'username': username, 'email': email})
            return render(request, 'alterpd.html', {'message': message, 'dict': dictionary})
        # 遍历record表校验密码与用户名
        for i in range(0, len(df1), 1):
            if username in df1.loc[i]['username'] and email in df1.iloc[i]['email']:
                message = '修改密码成功'
                action = False
                sql1 = "update record set password = '" + password + "' where username = '" + username + "';"
                engine.execute(sql1)
                return render(request, 'home.html', {'message': message})
        if action:
            message = '所输入的邮箱不是账号注册时的邮箱！'
            dictionary = json.dumps({'message': message, 'username': username, 'email': email})
            return render(request, 'alterpd.html', {'message': message, 'dict': dictionary})
